package com.gedmanager.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import com.gedcorp.gedmanager.MainActivity;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

	public static final String TABLE_BACKUP = "backup";
	public static final String BACKUP_ID = "_id";
	public static final String BACKUP_DATE = "date";
	
	/*********************************************************************************************/
	
	public static final String TABLE_LICENSE = "license";
	public static final String LICENSE_ID = "_id";
	public static final String LICENSE_LIMIT = "limit_date";
	public static final String LICENSE_INSTALL = "install_date";
	
	/*********************************************************************************************/
	
	public static final String TABLE_SELLING = "selling";
	public static final String COLUMN_ID = "_id";
	public static final String COLUMN_PRODUCT_ID = "product_id";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_AMOUNT = "amount";
	public static final String COLUMN_PRICE = "price";
	public static final String COLUMN_UNIT_SALE_PRICE = "unit_sale_price";
	public static final String COLUMN_SALE_PRICE = "sale_price";
	public static final String COLUMN_DATE = "date";
	public static final String COLUMN_INSTALLMENTS = "installments";
	public static final String COLUMN_PAYMENT_FORM = "payment";
	public static final String COLUMN_COMMENT = "comments";

	/*********************************************************************************************/
	
	public static final String TABLE_COST = "cost";
	public static final String COST_ID = "_id";
	public static final String COST_NAME = "name";
	public static final String COST_VALUE = "value";
	public static final String COST_DATE = "date";
	public static final String COST_COMMENT = "comments";

	/*********************************************************************************************/
	
	public static final String TABLE_STORE = "store";
	public static final String STORE_ID = "_id";
	public static final String STORE_NAME = "name";
	public static final String STORE_PRICE = "price";
	public static final String STORE_DATE = "creation_date";
	public static final String STORE_URL = "image_url";
	public static final String STORE_AMOUNT = "amount";
	
	/*********************************************************************************************/


	// private static final String DATABASE_NAME = "sellings.db";
	public static String DB_FILEPATH = Environment.getExternalStorageDirectory().getAbsolutePath()
			+ "/" + MainActivity.PACKAGE_NAME + "/db/database.db";

	// public static String DB_FILEPATH2 =
	// Environment.getExternalStorageDirectory().getAbsolutePath()+
	// "/kkk/sellingsCP.db";

	private static final int DATABASE_VERSION = 3;

	// Database creation sql statement
	
	private static final String DATABASE_CREATE_BACKUP = "create table " + TABLE_BACKUP + "("
			+ BACKUP_ID + " integer primary key autoincrement, " + BACKUP_DATE + " text not null);";
	
	
	private static final String DATABASE_CREATE_LICENSE = "create table " + TABLE_LICENSE + "("
			+ LICENSE_ID + " integer primary key autoincrement, " + LICENSE_INSTALL + " text not null," +  LICENSE_LIMIT +  " text not null);";
	
	private static final String DATABASE_CREATE_SELLINGS = "create table " + TABLE_SELLING + "("
			+ COLUMN_ID + " integer primary key autoincrement, " + COLUMN_PRODUCT_ID + " integer not null, " + COLUMN_NAME + " text not null," + COLUMN_AMOUNT + " integer not null,"
			+ " " + COLUMN_PRICE + " real not null," + " " + COLUMN_UNIT_SALE_PRICE + " real not null," + " " + COLUMN_SALE_PRICE + " real not null," + " " + COLUMN_DATE + " text not null," + " "
			+ COLUMN_INSTALLMENTS + " integer not null," + " " + COLUMN_PAYMENT_FORM
			+ " text not null," + " " + COLUMN_COMMENT + " text not null);";
	
	private static final String DATABASE_CREATE_COSTS = "create table " + TABLE_COST + "("
			+ COST_ID + " integer primary key autoincrement, " + COST_NAME + " text not null,"
			+ " " + COST_VALUE + " real not null," + " " + COST_DATE + " text not null," + " "
			+ COST_COMMENT + " text not null);";
	
	private static final String DATABASE_CREATE_STORE = "create table " + TABLE_STORE + "("
			+ STORE_ID + " integer primary key autoincrement, " + STORE_NAME + " text not null,"
			+ " " + STORE_PRICE + " real not null," + " " + STORE_DATE + " text not null," + " "
			+ STORE_URL + " text not null," + STORE_AMOUNT + " integer not null);";

	public MySQLiteHelper(Context context) {
		super(context, DB_FILEPATH, null, DATABASE_VERSION);
		Log.d("db path", DB_FILEPATH);

	}

	@Override
	public void onCreate(SQLiteDatabase database) {

		database.execSQL(DATABASE_CREATE_BACKUP);
		database.execSQL(DATABASE_CREATE_LICENSE);
		database.execSQL(DATABASE_CREATE_SELLINGS);
		database.execSQL(DATABASE_CREATE_COSTS);
		database.execSQL(DATABASE_CREATE_STORE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(MySQLiteHelper.class.getName(), "Upgrading database from version " + oldVersion
				+ " to " + newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BACKUP);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LICENSE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SELLING);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_COST);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_STORE);
		onCreate(db);
	}

	/**
	 * Copies the database file at the specified location over the current
	 * internal application database.
	 * */
	public boolean importDatabase(String dbPath) throws IOException {
		Log.w(MySQLiteHelper.class.getName(), "dbpath" + dbPath);
		// Close the SQLiteOpenHelper so it will commit the created empty
		// database to internal storage.
		close();
		File newDb = new File(dbPath);
		File oldDb = new File(DB_FILEPATH);
		if (newDb.exists()) {
			FileUtils.copyFile(new FileInputStream(newDb), new FileOutputStream(oldDb));
			// Access the copied database so SQLiteHelper will cache it and mark
			// it as created.
			getWritableDatabase().close();
			return true;
		}
		return false;
	}
}